/****************************************************
	文件：ExcelHandler.cs
	作者：HuskyT
	邮箱：1005240602@qq.com
	日期：2020/9/19 18:18:2
	功能：Excel 处理器 （处理一个Excel任务）
*****************************************************/

/*
 * Excel结构：
 * Excel 文件名：Store.xlsx => class：StoreScheme，内部List子项：StoreItem
 * 默认以第一列为 主key
 * 第一行：字段名（对应类中的字段名）
 * 第二行：字段描述
 * 第三行：字段类型
 * 第四行开始：具体数据
 */

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using Excel;
using Newtonsoft.Json;
using UnityEngine;

namespace HTUtility
{
    public class ExcelHandler
    {
        /// <summary>
        /// 默认的行数（前3行无具体数据）
        /// </summary>
        private const int DEFAULT_ROW_COUNT = 3;
        /// <summary>
        /// Excel 对应到 class 增加的后缀
        /// </summary>
        private const string CLASS_ADD_SUFFIX = "Scheme";
        /// <summary>
        /// Excel 对应到 List 增加的后缀
        /// </summary>
        private const string LIST_ADD_SUFFIX = "List";
        /// <summary>
        /// Excel 对应到 单个数据项 Item 增加的后缀
        /// </summary>
        private const string ITEM_ADD_SUFFIX = "Item";

        /// <summary>
        /// Excel文件路径
        /// </summary>
        private string mPath;
        /// <summary>
        /// Excel数据
        /// </summary>
        private DataSet mData;
        /// <summary>
        /// Excel 第一页的数据
        /// </summary>
        private DataTable mSheet;
        /// <summary>
        /// 数据是否合法
        /// </summary>
        private bool mIsValid;
        /// <summary>
        /// 数据行数
        /// </summary>
        private int mRowCnt;
        /// <summary>
        /// 数据列数
        /// </summary>
        private int mColCnt;

        /// <summary>
        /// 数据是否合法
        /// </summary>
        public bool IsValid => mIsValid;
        /// <summary>
        /// Excel 文件名
        /// </summary>
        public string Name { get; private set; }
        /// <summary>
        /// Excel 数据对应的 类名
        /// </summary>
        public string ClassName { get; private set; }
        /// <summary>
        /// Excel 对应的 List 名
        /// </summary>
        public string ListName { get; private set; }
        /// <summary>
        /// Excel 对应的 单个数据 Item 名
        /// </summary>
        public string ItemName { get; private set; }

        public ExcelHandler(string path)
        {
            mPath = path;
            FileStream fs = File.Open(path, FileMode.Open, FileAccess.Read);
            IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(fs);
            mData = reader.AsDataSet();
            mIsValid = CheckDataValid(out mSheet);
            if (mIsValid)
            {
                mRowCnt = mSheet.Rows.Count;
                mColCnt = mSheet.Columns.Count;
                Name = HelperUtil.GetFileName(path, ".xlsx");
                ClassName = Name + CLASS_ADD_SUFFIX;
                ListName = Name + LIST_ADD_SUFFIX;
                ItemName = Name + ITEM_ADD_SUFFIX;
            }
        }

        #region Public Function
        /// <summary>
        /// Excel 转化为 对象List
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <returns></returns>
        public List<T> ToObjList<T>() where T : class, new()
        {
            if (mIsValid == false) return null;
            List<T> ret = new List<T>();
            for (int i = DEFAULT_ROW_COUNT; i < mRowCnt; i++)
            {
                T obj = HelperUtil.CreateClassObj<T>();
                for (int j = 0; j < mColCnt; j++)
                {
                    string memberName = mSheet.Rows[0][j].ToString(); //第一行：字段名
                    object value = mSheet.Rows[i][j]; //字段值
                    HelperUtil.SetMemberValue(obj, memberName, value); //反射设置值
                }
                ret.Add(obj);
            }
            return ret;
        }
        /// <summary>
        /// Excel 转化为 对象（在生成CSharp，并编译后，才有用）
        /// </summary>
        /// <returns></returns>
        public object ToObj()
        {
            string fullClassName = Consts.NAME_SPACE + "." + ClassName;
            string subClassName = Consts.NAME_SPACE + "." + ItemName;
            object obj = HelperUtil.CreateClassObj(fullClassName); //反射创建对象
            object lst = HelperUtil.CreateList(Type.GetType(subClassName)); //反射创建List
            for (int i = DEFAULT_ROW_COUNT; i < mRowCnt; i++)
            {
                object addItem = HelperUtil.CreateClassObj(subClassName); //List中的元素
                for (int j = 0; j < mColCnt; j++)
                {
                    HelperUtil.SetMemberValue(addItem, mSheet.Rows[0][j].ToString(), mSheet.Rows[i][j].ToString());
                }
                HelperUtil.InvokeMethod(lst, "Add", paramArray: new object[] { addItem }); //Add 到 List
            }
            HelperUtil.SetMemberValue(obj, ListName, lst);
            return obj;
        }
        /// <summary>
        /// Excel 转化为 Csv
        /// </summary>
        /// <param name="csvPath">Csv路径</param>
        /// <param name="encoding">编码格式</param>
        public void ToCsv(string csvPath, Encoding encoding)
        {
            if (mIsValid == false) return;
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < mRowCnt; i++)
            {
                for (int j = 0; j < mColCnt; j++)
                {
                    sb.Append(mSheet.Rows[i][j] + ","); //使用 "," 分割数值
                }
                sb.Append("\r\n"); //使用 "\r\n" 换行
            }
            HelperUtil.WriteStringToFile(sb.ToString(), csvPath, encoding);
        }
        /// <summary>
        /// Excel 转化为 Xml
        /// </summary>
        /// <param name="xmlPath">Xml路径</param>
        /// <param name="encoding">编码格式</param>
        public void ToXml(string xmlPath, Encoding encoding)
        {
            if (mIsValid == false) return;
            try
            {
                StringBuilder sb = new StringBuilder();
                //Xml 文件头：
                sb.AppendLine("<?xml version=\"1.0\" encoding=\"utf-8\"?>");
                //根节点：
                sb.AppendLine($"<{ClassName}>");
                for (int i = DEFAULT_ROW_COUNT; i < mRowCnt; i++)
                {
                    //子节点：
                    sb.Append($"  <{ListName} ");
                    for (int j = 0; j < mColCnt; j++)
                    {
                        sb.Append($"{mSheet.Rows[0][j].ToString()}=\"{mSheet.Rows[i][j].ToString()}\"");
                        sb.Append(" ");
                    }
                    //闭合标签：
                    sb.Append("/>");
                    sb.Append("\r\n");
                }
                //闭合标签：
                sb.Append($"</{ClassName}>");
                HelperUtil.WriteStringToFile(sb.ToString(), xmlPath, encoding);
            }
            catch (Exception e)
            {
                Debug.LogError($"Excel to Xml error, Path : {mPath}\n Exception : {e.ToString()}");
            }
        }
        /// <summary>
        /// Excel 转化为 Json
        /// </summary>
        /// <param name="jsonPath">Json路径</param>
        /// <param name="encoding">编码格式</param>
        public void ToJson(string jsonPath, Encoding encoding)
        {
            if (mIsValid == false) return;
            try
            {
                List<Dictionary<string, object>> table = new List<Dictionary<string, object>>(); //数据容器
                for (int i = DEFAULT_ROW_COUNT; i < mRowCnt; i++)
                {
                    Dictionary<string, object> item = new Dictionary<string, object>();
                    for (int j = 0; j < mColCnt; j++)
                    {
                        string memberName = mSheet.Rows[0][j].ToString();
                        object value = mSheet.Rows[i][j];
                        if (item.ContainsKey(memberName))
                        {
                            Debug.LogError($"Excel to Json error, Same key is {memberName}, Path : {mPath}");
                            return;
                        }
                        item.Add(memberName, value);
                    }
                    table.Add(item);
                }
                string json = JsonConvert.SerializeObject(table, Newtonsoft.Json.Formatting.Indented);
                HelperUtil.WriteStringToFile(json, jsonPath, encoding);
            }
            catch (Exception e)
            {
                Debug.LogError($"Excel to Json error, Path : {mPath}\n Exception : {e.ToString()}");
            }
        }
        /// <summary>
        /// Excel 转化为 Lua
        /// </summary>
        /// <param name="luaPath">Lua路径</param>
        /// <param name="encoding">编码格式</param>
        public void ToLua(string luaPath, Encoding encoding)
        {
            if (mIsValid == false) return;
            try
            {
                StringBuilder sb = new StringBuilder();
                //Lua 文件头：
                sb.AppendLine($"local {ClassName} = {{");
                for (int i = DEFAULT_ROW_COUNT; i < mRowCnt; i++)
                {
                    //Item：
                    sb.AppendLine($"  [{mSheet.Rows[i][0]}] = {{");
                    for (int j = 0; j < mColCnt; j++)
                    {
                        sb.AppendLine($"    [\"{mSheet.Rows[0][j].ToString()}\"] = \"{mSheet.Rows[i][j].ToString()}\",");
                    }
                    sb.AppendLine("  },");
                }
                sb.AppendLine("}");
                sb.AppendLine($"return {ClassName}");
                HelperUtil.WriteStringToFile(sb.ToString(), luaPath, encoding);
            }
            catch (Exception e)
            {
                Debug.LogError($"Excel to Lua error, Path : {mPath}\n Exception : {e.ToString()}");
            }
        }
        /// <summary>
        /// Excel 转化为 CSharp
        /// </summary>
        /// <param name="csharpPath">CSharp路径</param>
        /// <param name="encoding">编码格式</param>
        public void ToCSharp(string csharpPath, Encoding encoding)
        {
            if (mIsValid == false) return;
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendLine($"//最后生成时间：{string.Concat(DateTime.Now.Year, "/", DateTime.Now.Month, "/", DateTime.Now.Day, " ", DateTime.Now.Hour, ":", DateTime.Now.Minute, ":", DateTime.Now.Second)}");
                sb.AppendLine();
                //Using
                sb.AppendLine("using System;");
                sb.AppendLine("using System.Collections.Generic;");
                sb.AppendLine("using System.Xml.Serialization;");
                sb.AppendLine();
                //命名空间
                sb.AppendLine($"namespace {Consts.NAME_SPACE}");
                sb.AppendLine("{");
                //类名
                sb.AppendLine("\t[Serializable]");
                sb.AppendLine($"\tpublic class {ClassName}");
                sb.AppendLine("\t{");
                //Member
                sb.AppendLine($"\t\t[XmlElement(\"{ListName}\")]");
                sb.AppendLine($"\t\tpublic List<{ItemName}> {ListName};");
                sb.AppendLine("\t}");

                //数据子项：
                sb.AppendLine("\t[Serializable]");
                sb.AppendLine($"\tpublic class {ItemName}");
                sb.AppendLine("\t{");
                //Member
                for (int j = 0; j < mColCnt; j++)
                {
                    sb.AppendLine($"\t\t[XmlAttribute(\"{mSheet.Rows[0][j]}\")]");
                    sb.AppendLine($"\t\tpublic {mSheet.Rows[2][j]} {mSheet.Rows[0][j]} {{ get; set; }}");
                }
                sb.AppendLine("\t}");
                sb.AppendLine("}");
                HelperUtil.WriteStringToFile(sb.ToString(), csharpPath, encoding);
            }
            catch (Exception e)
            {
                Debug.LogError($"Excel to CSharp error, Path : {mPath}\n Exception : {e.ToString()}");
            }
        }
        /// <summary>
        /// 转化为二进制（只在生成了 CSharp 后有效）
        /// </summary>
        /// <param name="binaryPath">二进制文件存储路径</param>
        public void ToBinary(string binaryPath)
        {
            try
            {
                object obj = ToObj();
                HelperUtil.ObjToBinary(binaryPath, obj);
            }
            catch (Exception e)
            {
                Debug.LogError($"Excel to Binary error, Path : {mPath}\n Exception : {e.ToString()}");
            }
        }
        #endregion

        #region Private Function
        /// <summary>
        /// 检验 Excel 数据合法性
        /// </summary>
        /// <param name="sheet">Excel中的第一页sheet的数据</param>
        /// <returns></returns>
        private bool CheckDataValid(out DataTable sheet)
        {
            if (mData.Tables.Count <= 0)
            {
                sheet = null;
                HTLogger.Error("Excel data is invalid, excel path is {0}", mPath);
                return false;
            }
            sheet = mData.Tables[0]; //默认只读取 Excel 第一张表的数据
            if (sheet.Rows.Count <= DEFAULT_ROW_COUNT)
            {
                HTLogger.Error("Excel data is invalid, excel path is {0}", mPath);
                return false; //前三行无具体数据
            }
            return true;
        }
        #endregion
    }
}
